using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using OneSoft.EntityCore;
using OneSoft.DataAccess;
using BySoft.Server.Common.Entity;
using BySoft.Server.Common.Schema;

namespace BySoft.Server.DataAccess
{
	public partial class MdCustomerDA
	{
		#region GetByID

		/// <summary>
		/// 取得实体
		/// </summary>
		/// <param name="id">实体关键字</param>
		/// <returns></returns>
		public static MdCustomerEntity GetMdCustomerByID(String id)
		{
			string strSQL = "SELECT * FROM " + MdCustomerTable.C_TABLENAME;
			WhereBuilder wb = new WhereBuilder(strSQL);
			wb.AddAndCondition(MdCustomerTable.C_CUSTOMERID, SQLOperator.Equal, id);
			return OneSoft.DataAccess.DataAccessCommon.SelectSingle<MdCustomerEntity>(wb);
		}

		#endregion

		#region GetAll

		/// <summary>
		/// 获取所有数据
		/// </summary>
		/// <returns>所有数据集合</returns>
		public static DataSet GetMdCustomerAll()
		{
			string strSQL = "SELECT * FROM " + MdCustomerTable.C_TABLENAME;
			return OneSoft.DataAccess.DataAccessCommon.SelectDataSet(strSQL);
		}

		#endregion

		#region GetByCondition

		/// <summary>
		/// 根据Condition获取DS
		/// </summary>
		/// <returns>所有数据集合</returns>
		public static DataSet GetDSByCondition(string strCondition)
		{
			string strSQL = "SELECT * FROM " + MdCustomerTable.C_TABLENAME;
			WhereBuilder wb = new WhereBuilder(strSQL);
			wb.AddCondition(strCondition);
			return OneSoft.DataAccess.DataAccessCommon.SelectDataSet(wb);
		}

		/// <summary>
		/// 根据Condition获取实体集合
		/// </summary>
		/// <param name="strCondition">SQL条件</param>
		/// <returns>实体集合</returns>
		public static List<MdCustomerEntity> GetByCondition(string strCondition)
		{
			string strSQL = "SELECT * FROM " + MdCustomerTable.C_TABLENAME;
			WhereBuilder wb = new WhereBuilder(strSQL);
			wb.AddCondition(strCondition);
			return OneSoft.DataAccess.DataAccessCommon.Select<MdCustomerEntity>(wb);
		}

		#endregion

        public static bool IsCustomerNameExists(string customerName)
        {
            bool isTag = false;
            string strSQL = "SELECT * FROM " + MdCustomerTable.C_TABLENAME;
            WhereBuilder wb = new WhereBuilder(strSQL);
            wb.AddCondition(" CustomerName='" + customerName + "'");
            DataSet ds = OneSoft.DataAccess.DataAccessCommon.SelectDataSet(wb);
            if (ds.Tables[0].Rows.Count > 0 && ds != null)
            {
                isTag = true;
            }
            return isTag;
        }

        public static bool IsCustomerNameExists(string customerName, string customerID)
        {
            bool isTag = false;
            string strSQL = "SELECT * FROM " + MdCustomerTable.C_TABLENAME;
            WhereBuilder wb = new WhereBuilder(strSQL);
            wb.AddCondition(" CustomerName='" + customerName + "' and CustomerID!='" + customerID + "'");
            DataSet ds = OneSoft.DataAccess.DataAccessCommon.SelectDataSet(wb);
            if (ds.Tables[0].Rows.Count > 0 && ds != null)
            {
                isTag = true;
            }
            return isTag;
        }
        private static string GetHeadSQL()
        {
            return @"SELECT A.*,a.CustomerID AS MatCustomerID, a.CustomerName AS MatCustomerName
                    FROM MD_Customer A 
                    WHERE 1=1";
        }
        public static DataTable SelectList(string strCustomerID, string strCustomerName, string UserID, int pageIndex, int pageSize, out int count)
        {
            try
            {
                #region SQL Server
                string strcmd = GetHeadSQL();

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strCustomerID))
                {
                    strcmd += " and A.CustomerID LIKE @CustomerID";
                    dpc.AddWithValue("@CustomerID", "%" + strCustomerID + "%");
                }

                if (!string.IsNullOrEmpty(strCustomerName))
                {
                    strcmd += " and A.CustomerName LIKE @CustomerName";
                    dpc.AddWithValue("@CustomerName", "%" + strCustomerName + "%");
                }
                if (!string.IsNullOrEmpty(UserID))
                {
                    strcmd += " and A.CreateUserId=@UserID";
                    dpc.AddWithValue("@UserID", UserID);
                }

                strcmd += " ORDER BY A.CustomerID DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable SelectDialogList(string strCustomerID, string strCustomerName, string UserID, int pageIndex, int pageSize, out int count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"SELECT A.*,cd.CustomerDetailID,
                                cd.ContactName,cd.Tel,cd.Job,cd.Moblie,cd.Email,cd.QQ
                                FROM MD_Customer A
                                left join (select cd.ContactName,cd.Moblie,cd.Tel,cd.Job,cd.Email,cd.QQ,c.CustomerID,c.CustomerDetailID from ( select c.CustomerID,min(cd.CustomerDetailID) CustomerDetailID from MD_Customer c
                                left join MD_CustomerDetail cd on cd.CustomerID=c.CustomerID
                                group by c.CustomerID) c
                                left join MD_CustomerDetail cd on cd.CustomerDetailID=c.CustomerDetailID) cd on a.CustomerID=cd.CustomerID
                                WHERE 1=1";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strCustomerID))
                {
                    strcmd += " and A.CustomerID LIKE @CustomerID";
                    dpc.AddWithValue("@CustomerID", "%" + strCustomerID + "%");
                }

                if (!string.IsNullOrEmpty(strCustomerName))
                {
                    strcmd += " and A.CustomerName LIKE @CustomerName";
                    dpc.AddWithValue("@CustomerName", "%" + strCustomerName + "%");
                }
                if (!string.IsNullOrEmpty(UserID))
                {
                    strcmd += " and A.CreateUserId=@UserID";
                    dpc.AddWithValue("@UserID", UserID);
                }

                strcmd += " ORDER BY A.CustomerID DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable SelectDialogListByCustomerContactRecord(string strCustomerID, string strCustomerName, string UserID, int pageIndex, int pageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"SELECT distinct A.*,a.CustomerID AS MatCustomerID, 
                                    a.CustomerName AS MatCustomerName,cd.ContactName,cd.Moblie
                                    FROM MD_Customer A 
                                    left join (select cd.ContactName,cd.Moblie,cd.Tel,cd.Job,cd.Email,cd.QQ,c.CustomerID,c.CustomerDetailID from ( select c.CustomerID,min(cd.CustomerDetailID) CustomerDetailID from MD_Customer c
                                    left join MD_CustomerDetail cd on cd.CustomerID=c.CustomerID
                                    group by c.CustomerID) c
                                    left join MD_CustomerDetail cd on cd.CustomerDetailID=c.CustomerDetailID) cd on a.CustomerID=cd.CustomerID where 1=1";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strCustomerID))
                {
                    strcmd += " and A.CustomerID LIKE @CustomerID";
                    dpc.AddWithValue("@CustomerID", "%" + strCustomerID + "%");
                }

                if (!string.IsNullOrEmpty(strCustomerName))
                {
                    strcmd += " and A.CustomerName LIKE @CustomerName";
                    dpc.AddWithValue("@CustomerName", "%" + strCustomerName + "%");
                }
                if (!string.IsNullOrEmpty(UserID))
                {
                    strcmd += " and A.CreateUserId=@UserID";
                    dpc.AddWithValue("@UserID", UserID);
                }

                strcmd += " ORDER BY A.CustomerID DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }
    }
}

